﻿using System;
using System.Collections.Generic;

namespace Dapper.Sharding
{
    internal class CsharpTypeToDbType
    {

        public static string Create(DataBaseType dbType, DataBaseVersion dbVersion, Type type, double length = 0, string columnType = null, int scale = 0)
        {
            if (!string.IsNullOrEmpty(columnType))
            {
                if (columnType == "jsons")
                {
                    if (dbType == DataBaseType.MySql)
                    {
                        return "longtext";
                    }
                    if (dbType == DataBaseType.Postgresql)
                    {
                        return "text";
                    }
                    if (dbType == DataBaseType.Sqlite)
                    {
                        return "TEXT";
                    }
                    if (dbType == DataBaseType.ClickHouse)
                    {
                        return "String";
                    }
                    if (dbType == DataBaseType.Oracle)
                    {
                        return "NCLOB";
                    }
                    if (dbType == DataBaseType.SqlServer)
                    {
                        return "nvarchar(max)";
                    }
                    if (dbType == DataBaseType.DuckDB)
                    {
                        return "VARCHAR";
                    }

                    if (dbType == DataBaseType.SapHana)
                    {
                        return "NCLOB";
                    }
                }

                if (dbType != DataBaseType.Postgresql)
                {
                    if (columnType == "json" || columnType == "jsonb")
                    {
                        if (dbType == DataBaseType.MySql)
                        {
                            return "json";
                        }
                        else if (dbType == DataBaseType.SqlServer)
                        {
                            return "nvarchar(max)";
                        }
                        else if (dbType == DataBaseType.ClickHouse)
                        {
                            return "String";
                        }
                        else if (dbType == DataBaseType.Oracle)
                        {
                            return "NCLOB";
                        }
                        else if (dbType == DataBaseType.DuckDB)
                        {
                            return "VARCHAR";
                        }
                        else if (dbType == DataBaseType.Sqlite)
                        {
                            return "TEXT";
                        }
                        else if (dbType == DataBaseType.SapHana)
                        {
                            return "NCLOB";
                        }
                        else
                        {
                            return "text";
                        }
                    }
                }

                if (dbType != DataBaseType.MySql)
                {
                    if (columnType == "longtext")
                    {
                        if (dbType == DataBaseType.SqlServer)
                        {
                            return "nvarchar(max)";
                        }
                        else if (dbType == DataBaseType.Oracle)
                        {
                            return "NCLOB";
                        }
                        else if (dbType == DataBaseType.ClickHouse)
                        {
                            return "String";
                        }
                        else if (dbType == DataBaseType.DuckDB)
                        {
                            return "VARCHAR";
                        }
                        else if (dbType == DataBaseType.Sqlite)
                        {
                            return "TEXT";
                        }
                        else if (dbType == DataBaseType.SapHana)
                        {
                            return "NCLOB";
                        }
                        else
                        {
                            return "text";
                        }
                    }
                }

                return columnType;
            }
            switch (dbType)
            {
                case DataBaseType.MySql: return CreateMySqlType(type, length, scale);
                case DataBaseType.Sqlite: return CreateSqliteType(type);
                case DataBaseType.SqlServer: return CreateSqlServerType(type, dbVersion, length, scale);
                case DataBaseType.Postgresql: return CreatePostgresqlType(type, length, scale);
                case DataBaseType.Oracle: return CreateOracleType(type, length, scale);
                case DataBaseType.ClickHouse: return CreateClickHouseType(type, length, scale);
                case DataBaseType.DuckDB: return CreateDuckDbType(type, length, scale);
                case DataBaseType.SapHana: return CreateSapHanaType(type, length, scale);
            }
            throw new Exception("CsharpTypeToDbType no found");
        }

        private static string UnknownTypeMessage(string t)
        {
            return $"unknown type {t}, please set ColumnAttribute ColumnType like [Column(columnType:\"jsonb\")] or other database type.";
        }

        private static string CreateSqlServerType(Type type, DataBaseVersion dbVersion, double length = 0, int scale = 0)
        {
            if (type == typeof(Guid) || type == typeof(Guid?))
            {
                if (length <= 0)
                {
                    length = 36;
                }
                return $"nvarchar({length})";
            }

            if (type == typeof(string))
            {
                if (length == -2)
                    return "text";
                if (length == -3)
                    return "ntext";
                if (length <= -1)
                    return "nvarchar(max)";
                if (length == 0)
                    length = 50;
                return $"nvarchar({length})";
            }

            if (type == typeof(bool) || type == typeof(bool?))
            {
                return "bit";
            }

            if (type == typeof(byte) || type == typeof(sbyte) || type == typeof(byte?) || type == typeof(sbyte?))
            {
                return "tinyint";
            }

            if (type == typeof(short) || type == typeof(ushort) || type == typeof(short?) || type == typeof(ushort?))
            {
                return "smallint";
            }

            if (type == typeof(int) || type.BaseType == typeof(Enum) || type == typeof(uint) || type == typeof(int?) || type == typeof(uint?))
            {
                return "int";
            }

            if (type == typeof(long) || type == typeof(ulong) || type == typeof(long?) || type == typeof(ulong?))
            {
                return "bigint";
            }

            if (type == typeof(float) || type == typeof(float?))
            {
                return "real";
            }

            if (type == typeof(double) || type == typeof(double?))
            {
                return "float";
            }

            if (type == typeof(decimal) || type == typeof(decimal?))
            {
                if (scale > 0)
                {
                    return $"decimal({Math.Floor(length)},{scale})";
                }
                var len = length.ToString();
                if (len.Contains("."))
                {
                    len = len.Replace(".", ",");
                    return $"decimal({len})";
                }
                if (length <= 0)
                    return "decimal(18,2)";
                return $"decimal({length},0)";
            }

            if (type == typeof(DateTime) || type == typeof(DateTime?))
            {
                if (dbVersion == DataBaseVersion.SqlServer2005)
                {
                    return "datetime";
                }
                if (length >= 0)
                {
                    if (length > 7)
                    {
                        length = 7;
                    }
                    return $"datetime2({length})";
                }
                if (length == -1)
                    return "date";
                if (length == -2)
                    return "timestamp";
                return "datetime";
            }

            if (type == typeof(DateTimeOffset) || type == typeof(DateTimeOffset?))
            {
                if (dbVersion == DataBaseVersion.SqlServer2005)
                {
                    return "datetime";
                }
                if (length >= 0)
                {
                    if (length > 7)
                    {
                        length = 7;
                    }
                }
                return $"datetimeoffset({length})";
            }

#if CORE6
            if (type == typeof(DateOnly) || type == typeof(DateOnly?))
            {
                if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.Date)
                {
                    return "date";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.DateTime)
                {
                    return "datetime2(0)";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.Number)
                {
                    return "int";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.String)
                {
                    return "varchar(10)";
                }
                return "int";
            }

            if (type == typeof(TimeOnly) || type == typeof(TimeOnly?))
            {
                if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.TimeSpan)
                {
                    return "time";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.Time)
                {
                    return "time";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.DateTime)
                {
                    return "datetime2(7)";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.Number)
                {
                    return "bigint";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.String)
                {
                    return "varchar(8)";
                }
                return "bigint";
            }
#endif

            throw new Exception(UnknownTypeMessage(type.Name));

            //if (length <= 0)
            //    length = 50;
            //return $"binary({length})";
        }

        private static string CreateMySqlType(Type type, double length = 0, int scale = 0)
        {
            if (type == typeof(Guid) || type == typeof(Guid?))
            {
                if (length <= 0)
                {
                    length = 36;
                }
                return $"varchar({length})";
            }

            if (type == typeof(string))
            {
                if (length == -1)
                    return "longtext";
                if (length == -2)
                    return "text";
                if (length == -3)
                    return "mediumtext";
                if (length == -4)
                    return "tinytext";
                if (length <= 0)
                    length = 50;
                return $"varchar({length})";
            }

            if (type == typeof(bool) || type == typeof(bool?))
            {
                return "bit(1)";
            }

            if (type == typeof(byte) || type == typeof(sbyte) || type == typeof(byte?) || type == typeof(sbyte?))
            {
                return "tinyint(4)";
            }

            if (type == typeof(short) || type == typeof(ushort) || type == typeof(short?) || type == typeof(ushort?))
            {
                return "smallint(6)";
            }

            if (type == typeof(int) || type.BaseType == typeof(Enum) || type == typeof(uint) || type == typeof(int?) || type == typeof(uint?))
            {
                return "int(11)";
            }

            if (type == typeof(long) || type == typeof(ulong) || type == typeof(long?) || type == typeof(ulong?))
            {
                return "bigint(20)";
            }

            if (type == typeof(float) || type == typeof(float?))
            {
                return "float";
            }

            if (type == typeof(double) || type == typeof(double?))
            {
                return "double";
            }

            if (type == typeof(decimal) || type == typeof(decimal?))
            {
                if (scale > 0)
                {
                    return $"decimal({Math.Floor(length)},{scale})";
                }
                var len = length.ToString();
                if (len.Contains("."))
                {
                    len = len.Replace(".", ",");
                    return $"decimal({len})";
                }
                if (length <= 0)
                    return "decimal(18,2)";
                return $"decimal({length},0)";
            }

            if (type == typeof(DateTime) || type == typeof(DateTime?))
            {
                if (length == 0)
                    return "datetime";
                if (length > 0)
                {
                    if (length > 6)
                    {
                        length = 6;
                    }
                    return $"datetime({length})";
                }
                if (length == -1)
                    return "datetime2";
                if (length == -2)
                    return "date";
                if (length == -3)
                    return "smalldatetime";
                return "timestamp";

            }

            if (type == typeof(DateTimeOffset) || type == typeof(DateTimeOffset?))
            {
                if (length == 0)
                    return "timestamp";

                if (length > 0)
                {
                    if (length > 6)
                    {
                        length = 6;
                    }
                }
                return $"timestamp({length})";
            }

#if CORE6
            if (type == typeof(DateOnly) || type == typeof(DateOnly?))
            {
                if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.Date)
                {
                    return "date";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.DateTime)
                {
                    return "datetime";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.Number)
                {
                    return "int(11)";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.String)
                {
                    return "varchar(10)";
                }
                return "int(11)";
            }

            if (type == typeof(TimeOnly) || type == typeof(TimeOnly?))
            {
                if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.TimeSpan)
                {
                    return "time";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.Time)
                {
                    return "time";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.DateTime)
                {
                    return "datetime(6)";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.Number)
                {
                    return "bigint(20)";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.String)
                {
                    return "varchar(8)";
                }
                return "bigint(20)";
            }
#endif

            throw new Exception(UnknownTypeMessage(type.Name));

            //if (length >= 0)
            //    return "blob";
            //if (length == -1)
            //    return "tinyblob";
            //if (length == -2)
            //    return "mediumblob";
            //return "longblob";

        }

        private static string CreateSqliteType(Type type)
        {
            if (type == typeof(Guid) || type == typeof(Guid?))
            {
                return "TEXT COLLATE NOCASE";
            }

            if (type == typeof(string))
            {
                return "TEXT COLLATE NOCASE";
            }

            if (type == typeof(bool) || type == typeof(bool?))
            {
                return "INTEGER";
            }

            if (type == typeof(byte) || type == typeof(sbyte) || type == typeof(byte?) || type == typeof(sbyte?))
            {
                return "INTEGER";
            }

            if (type == typeof(short) || type == typeof(ushort) || type == typeof(short?) || type == typeof(ushort?))
            {
                return "INTEGER";
            }

            if (type == typeof(int) || type.BaseType == typeof(Enum) || type == typeof(uint) || type == typeof(int?) || type == typeof(uint?))
            {
                return "INTEGER";
            }

            if (type == typeof(long) || type == typeof(ulong) || type == typeof(long?) || type == typeof(ulong?))
            {
                return "INTEGER";
            }

            if (type == typeof(float) || type == typeof(float?))
            {
                return "REAL";
            }

            if (type == typeof(double) || type == typeof(double?))
            {
                return "REAL";
            }

            if (type == typeof(decimal) || type == typeof(decimal?))
            {
                return "REAL";
            }

            if (type == typeof(DateTime) || type == typeof(DateTime?))
            {
                return "DATETIME";
            }

            if (type == typeof(DateTimeOffset) || type == typeof(DateTimeOffset?))
            {
                return "DATETIME";
            }

#if CORE6
            if (type == typeof(DateOnly) || type == typeof(DateOnly?))
            {
                if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.Date)
                {
                    return "DATE";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.DateTime)
                {
                    return "DATETIME";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.Number)
                {
                    return "INTEGER";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.String)
                {
                    return "TEXT";
                }
                return "INTEGER";
            }

            if (type == typeof(TimeOnly) || type == typeof(TimeOnly?))
            {
                if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.TimeSpan)
                {
                    return "DATETIME";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.Time)
                {
                    return "TIME";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.DateTime)
                {
                    return "DATETIME";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.Number)
                {
                    return "INTEGER";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.String)
                {
                    return "TEXT";
                }
                return "INTEGER";
            }
#endif

            throw new Exception(UnknownTypeMessage(type.Name));

            //return "BLOB";
        }

        private static string CreatePostgresqlType(Type type, double length = 0, int scale = 0)
        {
            if (type == typeof(Guid) || type == typeof(Guid?))
            {
                if (length <= 0)
                {
                    length = 36;
                }
                return $"varchar({length})";

            }

            if (type == typeof(string))
            {
                if (length == -1)
                    return "text";
                if (length == -10)
                    return "jsonb";
                if (length == -11)
                    return "json";
                if (length == -20)
                    return "geometry";
                if (length > -30 && length < -20)
                {
                    var str = length.ToString();
                    if (str.Contains("."))
                    {
                        var srid = str.Split('.')[1];
                        return $"geometry(geometry,{srid})";
                    }
                    else
                    {
                        return "geometry";
                    }
                }
                if (length <= 0)
                    length = 50;
                return $"varchar({length})";

            }

            if (type == typeof(bool) || type == typeof(bool?))
            {
                return "bool";
            }

            if (type == typeof(byte) || type == typeof(sbyte) || type == typeof(byte?) || type == typeof(sbyte?))
            {
                return "int2";
            }

            if (type == typeof(short) || type == typeof(ushort) || type == typeof(short?) || type == typeof(ushort?))
            {
                return "int2";
            }

            if (type == typeof(int) || type.BaseType == typeof(Enum) || type == typeof(uint) || type == typeof(int?) || type == typeof(uint?))
            {
                return "int4";
            }

            if (type == typeof(long) || type == typeof(ulong) || type == typeof(long?) || type == typeof(ulong?))
            {
                return "int8";
            }

            if (type == typeof(float) || type == typeof(float?))
            {
                return "float4";
            }

            if (type == typeof(double) || type == typeof(double?))
            {
                return "float8";
            }

            if (type == typeof(decimal) || type == typeof(decimal?))
            {
                if (scale > 0)
                {
                    return $"numeric({Math.Floor(length)},{scale})";
                }
                var len = length.ToString();
                if (len.Contains("."))
                {
                    len = len.Replace(".", ",");
                    return $"numeric({len})";
                }
                if (length <= 0)
                    return "numeric(18,2)";
                return $"numeric({length},0)";
            }

            if (type == typeof(DateTime) || type == typeof(DateTime?))
            {
                if (length >= 0)
                {
                    if (length > 6)
                    {
                        length = 6;
                    }
                    return $"timestamp({length})";
                }
                if (length == -1)
                    return "timestamptz";
                return "date";
            }

            if (type == typeof(DateTimeOffset) || type == typeof(DateTimeOffset?))
            {
                if (length > 6)
                {
                    length = 6;
                }
                return $"timestamptz({length})";
            }

            if (type == typeof(TimeSpan) || type == typeof(TimeSpan?))
            {
                if (length >= 0)
                    return "time";
                return "interval";
            }

#if CORE6
            if (type == typeof(DateOnly) || type == typeof(DateOnly?))
            {
                if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.Date)
                {
                    return "date";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.DateTime)
                {
                    return "timestamp(0)";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.Number)
                {
                    return "int4";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.String)
                {
                    return "varchar(10)";
                }
                return "int4";
            }

            if (type == typeof(TimeOnly) || type == typeof(TimeOnly?))
            {
                if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.TimeSpan)
                {
                    return "time";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.Time)
                {
                    return "timestamp(6)";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.DateTime)
                {
                    return "timestamp(6)";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.Number)
                {
                    return "int8";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.String)
                {
                    return "varchar(8)";
                }
                return "int8";
            }
#endif

            var typeList = new List<string>
            {
                "Point", "MultiPoint", "LineString",
                "MultiLineString", "Polygon", "MultiPolygon", "GeometryCollection",
                "Feature","FeatureCollection","GeoJSONObject","Geometry"
            };

            if (typeList.Contains(type.Name))
            {
                return "geometry";
            }
            throw new Exception(UnknownTypeMessage(type.Name));

            //return "bytea";

        }

        private static string CreateOracleType(Type type, double length = 0, int scale = 0)
        {
            if (type == typeof(Guid) || type == typeof(Guid?))
            {
                if (length <= 0)
                {
                    length = 36;
                }
                return $"NVARCHAR2({length})";

            }

            if (type == typeof(string))
            {
                if (length == -1)
                    return "NCLOB";
                if (length == -2)
                    return "CLOB";
                if (length <= 0)
                    length = 50;
                return $"NVARCHAR2({length})";

            }

            if (type == typeof(bool) || type == typeof(bool?))
            {
                return "NUMBER(1)";
            }

            if (type == typeof(byte) || type == typeof(sbyte) || type == typeof(byte?) || type == typeof(sbyte?))
            {
                return "NUMBER(3)";
            }

            if (type == typeof(short) || type == typeof(ushort) || type == typeof(short?) || type == typeof(ushort?))
            {
                return "NUMBER(5)";
            }

            if (type == typeof(int) || type.BaseType == typeof(Enum) || type == typeof(uint) || type == typeof(int?) || type == typeof(uint?))
            {
                return "NUMBER(10)";
            }

            if (type == typeof(long) || type == typeof(ulong) || type == typeof(long?) || type == typeof(ulong?))
            {
                return "NUMBER(19)";
            }

            if (type == typeof(float) || type == typeof(float?))
            {
                return "NUMBER(7,3)";
            }

            if (type == typeof(double) || type == typeof(double?))
            {
                return "NUMBER(15,5)";
            }

            if (type == typeof(decimal) || type == typeof(decimal?))
            {
                if (scale > 0)
                {
                    return $"NUMBER({Math.Floor(length)},{scale})";
                }
                if (length == 0)
                {
                    return "NUMBER";
                }

                var len = length.ToString();
                if (len.Contains("."))
                {
                    len = len.Replace(".", ",");
                    return $"NUMBER({len})";
                }
                if (length < 0)
                {
                    return "NUMBER(18,2)";
                }
                return $"NUMBER({length},0)";
            }

            if (type == typeof(DateTime) || type == typeof(DateTime?))
            {
                if (length == 0)
                {
                    return "DATE";
                }
                if (length == -1)
                {
                    return "TIMESTAMP";
                }
                if (length > 6)
                {
                    length = 6;
                }
                return $"TIMESTAMP({length})";
            }

            if (type == typeof(DateTimeOffset) || type == typeof(DateTimeOffset?))
            {
                if (length == 0)
                {
                    return "TIMESTAMP WITH TIME ZONE";
                }
                if (length > 6)
                {
                    length = 6;
                }
                return $"TIMESTAMP({length}) WITH TIME ZONE";
            }
#if CORE6
            if (type == typeof(DateOnly) || type == typeof(DateOnly?))
            {
                if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.Date)
                {
                    return "DATE";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.DateTime)
                {
                    return "TIMESTAMP";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.Number)
                {
                    return "NUMBER(9)";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.String)
                {
                    return "VARCHAR2(10)";
                }
                return "NUMBER(9)";
            }

            if (type == typeof(TimeOnly) || type == typeof(TimeOnly?))
            {
                if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.TimeSpan)
                {
                    return "TIME";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.Time)
                {
                    return "TIME";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.DateTime)
                {
                    return "TIMESTAMP";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.Number)
                {
                    return "NUMBER(19)";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.String)
                {
                    return "VARCHAR2(8)";
                }
                return "NUMBER(19)";
            }
#endif

            throw new Exception(UnknownTypeMessage(type.Name));

            //return "BLOB";
        }

        private static string CreateClickHouseType(Type type, double length = 0, int scale = 0)
        {
            if (type == typeof(Guid) || type == typeof(Guid?))
            {
                return $"UUID";
            }

            if (type == typeof(string))
            {
                if (length > 0 && ShardingFactory.ClickHouseFixedString)
                {
                    return $"FixedString({length})";
                }
                return "String";
            }

            if (type == typeof(bool) || type == typeof(bool?))
            {
                return "Bool";
            }

            if (type == typeof(DateTime) || type == typeof(DateTimeOffset) || type == typeof(DateTime?) || type == typeof(DateTimeOffset?))
            {
                if (length == -1)
                {
                    return "Date";
                }
                if (length == -2)
                {
                    return "Datetime64";
                }
                return "Datetime";
            }

            if (type == typeof(float) || type == typeof(float?))
            {
                return "Float32";
            }

            if (type == typeof(double) || type == typeof(double?))
            {
                return "Float64";
            }

            if (type == typeof(sbyte) || type == typeof(sbyte?))
            {
                return "Int8";
            }

            if (type == typeof(short) || type == typeof(short?))
            {
                return "Int16";
            }

            if (type == typeof(int) || type.BaseType == typeof(Enum) || type == typeof(int?))
            {
                if (length == -1)
                {
                    return "Int16";
                }
                if (length == -2)
                {
                    return "Int8";
                }
                return "Int32";
            }

            if (type == typeof(long) || type == typeof(long?))
            {
                return "Int64";
            }

            if (type == typeof(byte) || type == typeof(byte?))
            {
                return "UInt8";
            }

            if (type == typeof(ushort) || type == typeof(ushort?))
            {
                return "UInt16";
            }

            if (type == typeof(uint) || type == typeof(uint?))
            {
                return "UInt32";
            }

            if (type == typeof(ulong) || type == typeof(ulong?))
            {
                return "UInt64";
            }

            if (type == typeof(decimal) || type == typeof(decimal?))
            {
                if (length <= 0)
                {
                    length = 18.2;
                }
                var p = 1;
                var s = 0;
                var len = length.ToString();
                if (len.Contains("."))
                {
                    var arr = len.Split('.');
                    p = Convert.ToInt32(arr[0]);
                    s = Convert.ToInt32(arr[1]);
                }
                else
                {
                    p = Convert.ToInt32(len);
                }

                if (scale > 0)
                {
                    s = scale;
                }

                if (p >= 1 && p <= 9)
                {
                    return $"Decimal32({s})";
                }

                if (p >= 10 && p <= 18)
                {
                    return $"Decimal64({s})";
                }

                if (p >= 19 && p <= 38)
                {
                    return $"Decimal128({s})";
                }

                return $"Decimal32({s})";
            }

#if CORE6
            if (type == typeof(DateOnly) || type == typeof(DateOnly?))
            {
                if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.Date)
                {
                    return "Date";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.DateTime)
                {
                    return "Datetime";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.Number)
                {
                    return "Int32";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.String)
                {
                    return "String";
                }
                return "Int32";
            }

            if (type == typeof(TimeOnly) || type == typeof(TimeOnly?))
            {
                if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.TimeSpan)
                {
                    return "Datetime64";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.Time)
                {
                    return "Datetime64";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.DateTime)
                {
                    return "Datetime64";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.Number)
                {
                    return "Int64";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.String)
                {
                    return "String";
                }
                return "Int64";
            }
#endif

            return "String";
        }

        private static string CreateDuckDbType(Type type, double length = 0, int scale = 0)
        {
            if (type == typeof(Guid) || type == typeof(Guid?))
            {
                if (length == 0)
                {
                    return $"UUID";
                }
                return "VARCHAR";
            }

            if (type == typeof(string))
            {
                return $"VARCHAR";

            }

            if (type == typeof(bool) || type == typeof(bool?))
            {
                return "BOOLEAN";
            }

            if (type == typeof(byte) || type == typeof(sbyte) || type == typeof(byte?) || type == typeof(sbyte?))
            {
                return "TINYINT";
            }

            if (type == typeof(short) || type == typeof(ushort) || type == typeof(short?) || type == typeof(ushort?))
            {
                return "SMALLINT";
            }

            if (type == typeof(int) || type.BaseType == typeof(Enum) || type == typeof(uint) || type == typeof(int?) || type == typeof(uint?))
            {
                return "INTEGER";
            }

            if (type == typeof(long) || type == typeof(ulong) || type == typeof(long?) || type == typeof(ulong?))
            {
                return "BIGINT";
            }

            if (type == typeof(float) || type == typeof(float?))
            {
                return "REAL";
            }

            if (type == typeof(double) || type == typeof(double?))
            {
                return "DOUBLE";
            }

            if (type == typeof(decimal) || type == typeof(decimal?))
            {
                if (scale > 0)
                {
                    return $"DECIMAL({Math.Floor(length)},{scale})";
                }
                var len = length.ToString();
                if (len.Contains("."))
                {
                    len = len.Replace(".", ",");
                    return $"DECIMAL({len})";
                }
                if (length <= 0)
                    return "DECIMAL(18,2)";
                return $"DECIMAL({length},0)";
            }

            if (type == typeof(DateTime) || type == typeof(DateTime?))
            {
                return $"DATETIME";
            }

            if (type == typeof(DateTimeOffset) || type == typeof(DateTimeOffset?))
            {
                if (length >= 0)
                {
                    return "TIMESTAMPTZ";
                }
                return "TIMETZ";
            }

            if (type == typeof(TimeSpan) || type == typeof(TimeSpan?))
            {
                if (length >= 0)
                    return "TIME";
                return "INTERVAL";
            }

            if (type == typeof(IList<int>) || type == typeof(List<int>) || type == typeof(IEnumerable<int>) || type == typeof(int[]))
            {
                return "INTEGER[]";
            }

            if (type == typeof(IList<long>) || type == typeof(List<long>) || type == typeof(IEnumerable<long>) || type == typeof(long[]))
            {
                return "BIGINT[]";
            }

            if (type == typeof(IList<string>) || type == typeof(List<string>) || type == typeof(IEnumerable<string>) || type == typeof(string[]))
            {
                return "VARCHAR[]";
            }


#if CORE6
            if (type == typeof(DateOnly) || type == typeof(DateOnly?))
            {
                if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.Date)
                {
                    return "DATE";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.DateTime)
                {
                    return "TIMESTAMP_NS";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.Number)
                {
                    return "INTEGER";
                }
                else if (ShardingFactory.DateOnlyFormat == DbTypeDateOnly.String)
                {
                    return "VARCHAR";
                }
                return "INTEGER";
            }

            if (type == typeof(TimeOnly) || type == typeof(TimeOnly?))
            {
                if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.TimeSpan)
                {
                    return "TIME";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.Time)
                {
                    return "TIME";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.DateTime)
                {
                    return "BIGINT";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.Number)
                {
                    return "BIGINT";
                }
                else if (ShardingFactory.TimeOnlyFormat == DbTypeTimeOnly.String)
                {
                    return "VARCHAR";
                }
                return "BIGINT";
            }
#endif

            throw new Exception(UnknownTypeMessage(type.Name));

            //return "bytea";

        }

        private static string CreateSapHanaType(Type type, double length = 0, int scale = 0)
        {
            if (type == typeof(Guid) || type == typeof(Guid?))
            {
                if (length <= 0)
                {
                    length = 36;
                }
                return $"NVARCHAR({length})";
            }

            if (type == typeof(string))
            {
                if (length == -1)
                {
                    return "NCLOB";
                }

                if (length <= 0)
                {
                    length = 50;
                }

                return $"NVARCHAR({length})";
            }

            if (type == typeof(bool) || type == typeof(bool?))
            {
                return "BOOLEAN";
            }

            if (type == typeof(byte) || type == typeof(sbyte) || type == typeof(byte?) || type == typeof(sbyte?))
            {
                return "TINYINT";
            }

            if (type == typeof(short) || type == typeof(ushort) || type == typeof(short?) || type == typeof(ushort?))
            {
                return "SMALLINT";
            }

            if (type == typeof(int) || type.BaseType == typeof(Enum) || type == typeof(uint) || type == typeof(int?) || type == typeof(uint?))
            {
                return "INTEGER";
            }

            if (type == typeof(long) || type == typeof(ulong) || type == typeof(long?) || type == typeof(ulong?))
            {
                return "BIGINT";
            }

            if (type == typeof(float) || type == typeof(float?))
            {
                return "REAL";
            }

            if (type == typeof(double) || type == typeof(double?))
            {
                return "DOUBLE";
            }

            if (type == typeof(decimal) || type == typeof(decimal?))
            {
                if (scale > 0)
                {
                    return $"DECIMAL({Math.Floor(length)},{scale})";
                }
                var len = length.ToString();
                if (len.Contains("."))
                {
                    len = len.Replace(".", ",");
                    return $"DECIMAL({len})";
                }
                if (length <= 0)
                    return "DECIMAL(18,2)";
                return $"DECIMAL({length},0)";
            }

            if (type == typeof(DateTime) || type == typeof(DateTime?))
            {
                return "TIMESTAMP";
            }

            if (type == typeof(byte[]) || type == typeof(byte?[]))
            {
                return "BLOB";
            }

            throw new Exception(UnknownTypeMessage(type.Name));
        }
    }
}
